package br.ufal.tci.nexos.arcolive.persistence;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;

import br.ufal.tci.nexos.arcolive.ArCoLIVEServerProperties;
import br.ufal.tci.nexos.arcolive.logger.ArCoLIVELogRepository;
import br.ufal.tci.nexos.arcolive.util.Crypt;

public class GenericDatabaseManager {

    private Connection connection;
    private Statement statement = null;
    private int rowsAffected;
    private String hostname = "localhost";
    private String port = "3306";
    private String SGBD = "mysql";
    private String database = "arcolive";
    private String username = "root";
    private String password = "";
    private String databaseDriver = "org.gjt.mm.mysql.Driver";
    private boolean connected = false;
    private boolean transactionStarted = false;

    public GenericDatabaseManager(boolean autoconnect) throws SQLException {
    	this.loadParameters();
        if (autoconnect) {
        	this.connect();
        }
    }
    
    public GenericDatabaseManager() {
    	this.loadParameters();
    }

    private void loadParameters() {
        ArCoLIVEServerProperties properties = ArCoLIVEServerProperties.getInstance();
        Crypt crypt = new Crypt("");
        /*this.hostname = crypt.decrypt(properties.getStringProperty("persistence.hostname"));
        this.database = crypt.decrypt(properties.getStringProperty("persistence.database"));
        this.port = crypt.decrypt(properties.getStringProperty("persistence.port"));
        this.password = crypt.decrypt(properties.getStringProperty("persistence.password"));
        this.SGBD = properties.getStringProperty("persistence.SGBD");
        this.databaseDriver = properties.getStringProperty("persistence.databaseDriver");*/
        
        System.out.println("Hostname: "+hostname);
        System.out.println("Database: "+database);
        System.out.println("port: "+port);
        System.out.println("password: "+password);
        System.out.println("SGDB: "+SGBD);
        System.out.println("databaseDriver: "+databaseDriver+"\n\n");
    }
    
    public void connect() throws SQLException {
    	System.out.println("connect()");
        try {
            Class.forName(databaseDriver);
        } catch (ClassNotFoundException cnfe) {
            System.out.print("ClassNotFoundException: " + cnfe.getMessage());
        }

        System.out.print("Trying to connect to DBMS " + SGBD + "/" + database + "...!\n");
        System.out.println("jdbc:" + SGBD.toLowerCase() + "://" + hostname + ":" + port + "/" + database);
        this.connection = DriverManager.getConnection("jdbc:" + SGBD.toLowerCase() + "://" + hostname + ":" + port + "/" + database, username, password);
        this.connected = true;
        System.out.print("Connection estabilished!\n");
        this.statement = this.connection.createStatement();
               
    }
    
	public boolean isConnected() {
		return connected;
	}
	
	/*public GenericResultRow queryOneRecord(String query) {
		GenericResultSet result = this.queryAllRecords(query);
		if (result.next()) {
			return result.getCurrentRow();
		} else {
			return
		}
	}*/
	
	public GenericResultSet queryOneRecord(String query) {
		GenericResultSet result = this.queryAllRecords(query);
		result.toFirst();
		return result;
	}
		
    public GenericResultSet queryAllRecords(String query) {
    	System.out.println("queryAllRecords");
    	Statement statement;
    	ResultSet resultSet;
    	
    	GenericResultSet result = null;
    	GenericMetaDataColumn[] metaData = null;
    	/**try {
    		connect();
		} catch (Exception e) {
			System.out.println("Caiu aqui!!");
			// TODO: handle exception
		}*/
    	
    	
		try {
			
			statement = this.connection.createStatement();
			resultSet = statement.executeQuery(query);
			ResultSetMetaData md = resultSet.getMetaData();
			
			int columnCount;
			metaData = new GenericMetaDataColumn[md.getColumnCount()];
			columnCount = md.getColumnCount();
			
			int rowsCount = this.getRowsNumber(resultSet);
			
			
			for (int i = 1; i <= columnCount; i++) {
				GenericMetaDataColumn columnMeta = new GenericMetaDataColumn(md.getColumnName(i), md.getColumnClassName(i));
				metaData[i-1] = columnMeta;
			}
			
			if (rowsCount > 0) {
				result = new GenericResultSet(metaData);
				
				while (resultSet.next()) {
					GenericResultRow row = new GenericResultRow();
					for (int i = 1; i <= columnCount; i++) {
						row.put(resultSet.getString(i));
					}
					result.addRow(row);
				}
				return result;
			} else {
				result = new GenericResultSet(metaData);
			}
		} catch (SQLException e) {
			ArCoLIVELogRepository.getInstance().log(e.getMessage());
			result = new GenericResultSet(metaData);
		}
		result = new GenericResultSet(metaData);
		return result;
    }
    
    public boolean queryInTransaction(String SQL, boolean autocommit) {
    	return this.queryInTransaction(new String[]{SQL}, autocommit);
    }
    
    public boolean queryInTransaction(String SQLs[], boolean autocommit) {
    	
    	if (!this.startTransaction()) {
    		return false;
    	}
    	
    	for (int i = 0; i < SQLs.length; i++) {
			if (!this.executeQuery(SQLs[i])) {
				if (autocommit) {
					this.rollback();
				}
				return false;
			}
		}
    	
    	if (autocommit) {
    		return this.commit();
    	}
    	
    	return true;
    	
    }
    
    public boolean executeQuery(String query)  {
		try {
			this.rowsAffected = this.statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
		} catch (SQLException e) {
			this.rowsAffected = 0;
			return false;
		}
    	return true;
    }
    
    public int getLastInsertedId() {
    	try {
	    	ResultSet resultSet = this.statement.getGeneratedKeys();
	        return (resultSet.next()) ? resultSet.getInt(1) : -1;
    	} catch (SQLException sqle) {
    		return -1;
    	}
    }
    
    public int getRowsNumber(ResultSet resultSet) throws SQLException {    	
    	int counter;
    	resultSet.last();
    	counter = resultSet.getRow();
    	resultSet.beforeFirst();
    	return counter;
    }
    
    public boolean startTransaction() {
    	if (!this.transactionStarted) {

	       	try {
				this.connection.setAutoCommit(false);
				this.transactionStarted = true;
				return true;
			} catch (SQLException e) {
				return false;
			}
			
    	}
    	
    	return true;    	
       	
    }
    
    public boolean commit() {
    	if (this.transactionStarted) {
    		try {
				this.connection.commit();
				this.connection.setAutoCommit(true);
			} catch (SQLException e) {
				return false;
			}
    		
    		this.transactionStarted = false;
    		return true;
    	}
    	return false;
    }
    
    public boolean rollback() {
    	if (this.transactionStarted) {
    		try {
				this.connection.rollback();
				this.connection.setAutoCommit(true);
			} catch (SQLException e) {
				return false;
			}
    		
    		this.transactionStarted = false;
    	}
    	return false;
    } 

	public int getRowsAffected() {
		return this.rowsAffected;
	}

    public static void main(String[] args) throws SQLException {
    	GenericDatabaseManager gdm = new GenericDatabaseManager(true);
    	
    	GenericResultSet rs = gdm.queryAllRecords("SELECT * FROM Country");
    	while (rs.next()) {
    		ArCoLIVELogRepository.getInstance().log(rs.getType("countryId"));
    	}
    	
    	
    	/*String SQL1 = "INSERT INTO Server VALUES ('', 'name A', 'ip F')";
    	String SQL2 = "UPDATE Server SET serverName = 'name ARCOLIVE NOVO' WHERE serverId = ";
    	
    	if (gdm.queryInTransaction(SQL1, false)) {
    		int id = gdm.getLastInsertedId();
    		gdm.queryInTransaction(SQL2 + id, true);
    	} else {
    		gdm.rollback();
    	}*/
    	
	
    }
	
}